package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOcustomers;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOcustomer;

public class DAOcustomersMySQL implements DAOcustomers
{
	private DBManager dbManager;
	
	public DAOcustomersMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertCustomer(VOcustomer vOcustomer) 
	{
		int code = -1;
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("insert INTO customers (name,city, address, telephone) VALUES (?,?,?,?)");
			
			preparedStatement.setString(1, vOcustomer.getName());
			preparedStatement.setInt(2, vOcustomer.getCity());
			preparedStatement.setString(3, vOcustomer.getAddress());
			preparedStatement.setString(4, vOcustomer.getTelephone());
			
			preparedStatement.execute();
			
			preparedStatement = connection.prepareStatement("SELECT code FROM customers ORDER BY code DESC LIMIT 1");
			
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			code = resultSet.getInt(1);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return code;
	}

	public boolean updateCustomer(VOcustomer vOcustomer) 
	{
		boolean updated = false;
		
		PreparedStatement preparedStatement = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("UPDATE customers SET name=?, city=?, address=?, telephone=? WHERE code=?");
			
			preparedStatement.setString(1, vOcustomer.getName());
			preparedStatement.setInt(2, vOcustomer.getCity());
			preparedStatement.setString(3, vOcustomer.getAddress());
			preparedStatement.setString(4, vOcustomer.getTelephone());
			preparedStatement.setInt(5, vOcustomer.getCode());
			
			preparedStatement.execute();
			
			updated = true;
			
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return updated;
	}

	
	public boolean removeCustomer(VOcustomer vOcustomer)
	{
		boolean removed = false;
		
		PreparedStatement preparedStatement = null;
		
		Connection connection = dbManager.getConnection();
		
		try
		{
			preparedStatement = connection.prepareStatement("delete from customers where code=?");
			
			preparedStatement.setInt(1, vOcustomer.getCode());
			preparedStatement.execute();
			
			removed = true;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return removed;
	}
	
	public ArrayList<VOcustomer> listCustomers() 
	{
		ArrayList<VOcustomer>customers = new ArrayList<VOcustomer>();
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,city,address,telephone FROM customers");
			
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{
				VOcustomer customer = new VOcustomer();
				
				customer.setCode(resultSet.getInt(1));
				customer.setName(resultSet.getString(2));
				customer.setCity(resultSet.getInt(3));
				customer.setAddress(resultSet.getString(4));
				customer.setTelephone(resultSet.getString(5));
				
				customers.add(customer);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return customers;
	}

	public VOcustomer searchCustomerByCode(VOcustomer vOcustomer) 
	{
		VOcustomer customer = null;
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,city,address,telephone FROM customers WHERE code=?");
			
			preparedStatement.setInt(1, vOcustomer.getCode());
			
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			customer = new VOcustomer();
			
			customer.setCode(resultSet.getInt(1));
			customer.setName(resultSet.getString(2));
			customer.setCity(resultSet.getInt(3));
			customer.setAddress(resultSet.getString(4));
			customer.setTelephone(resultSet.getString(5));
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return customer;
	}

	public VOcustomer searchCustomerByName(VOcustomer vOcustomer) 
	{
VOcustomer customer = null;
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,city,address,telephone FROM customers WHERE name=?");
			
			preparedStatement.setString(1, vOcustomer.getName());
			
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			customer = new VOcustomer();
			
			customer.setCode(resultSet.getInt(1));
			customer.setName(resultSet.getString(2));
			customer.setCity(resultSet.getInt(3));
			customer.setAddress(resultSet.getString(4));
			customer.setTelephone(resultSet.getString(5));
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return customer;
	}

	
	
}
